﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class DocumentoAlmacenDetalleDatos

    Sub registrarDocumentoAlmacenDetalle(dad As DocumentoAlmacenDetalle)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "INSERT INTO Documento_Almacen_Detalle(CIA,SUC, " &
                                  "TDOC_Codigo,DCA_Codigo,DAD_Codigo,ART_Codigo,DAD_Cantidad) " &
                                  "VALUES(@cia,@suc,@tdoc,@dcacodigo,@dadcodigo,@artcodigo,@cantidad)"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@cia", dad.CIA)
                .Parameters.AddWithValue("@suc", dad.SUC_Codigo)
                .Parameters.AddWithValue("@tdoc", dad.TDOC_Codigo)
                .Parameters.AddWithValue("@dcaCodigo", dad.DCA_Codigo)
                .Parameters.AddWithValue("@dadcodigo", dad.DAD_Codigo)
                .Parameters.AddWithValue("@artcodigo", dad.ART_Codigo)
                .Parameters.AddWithValue("@cantidad", dad.CANTIDAD)
                .ExecuteNonQuery()
            End With

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try
    End Sub

    Function generarCodigoDAD(tipodoc As String) As Integer
        Dim codigo As Integer
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT COUNT(*) + 1 AS Contar " &
                                  "FROM Documento_Almacen_Detalle WHERE TDOC_Codigo = '" & tipodoc & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            Dim ds As New DataSet
            adapter.Fill(ds)

            codigo = CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim)

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return codigo
    End Function

    Sub desactivarRegistrosDAD(cia As String, suc As String, tdoc As String, dcaCodigo As String, dadCodigo As String)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "UPDATE Documento_Almacen_Detalle " &
                                  "SET DAD_Activo = '0' " &
                                  "WHERE CIA = @cia AND SUC = @suc " &
                                  "AND TDOC_Codigo = @tdoc " &
                                  "AND DCA_Codigo = @dcaCodigo AND DAD_Codigo = @dadCodigo " &
                                  "AND DAD_Activo = '1'"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@cia", cia)
                .Parameters.AddWithValue("@suc", suc)
                .Parameters.AddWithValue("@tdoc", tdoc)
                .Parameters.AddWithValue("@dcaCodigo", dcaCodigo)
                .Parameters.AddWithValue("@dadCodigo", dadCodigo)
                .ExecuteNonQuery()
            End With

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try
    End Sub

    Function obtenerArtículosEnsamblado(codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT ART_Codigo_Combo,ARE_Cantidad " &
                                  "FROM ArticuloEnsamblado " &
                                  "WHERE ART_Codigo = @articulo " &
                                  "AND ARE_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@articulo", codArticulo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerUbicaciones(codArticulo As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT UBI_Codigo, UAR_Stock - ASIG_UBI AS Stock " &
                                  "FROM ArticuloUbicacion " &
                                  "WHERE ART_Codigo = @articulo " &
                                  "AND UAR_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@articulo", codArticulo)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerDatosImpresionLargos(codPedido As String, tipDocumento As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT '*' + RTRIM(da.ART_Codigo) + '*' AS Barra, r.RUT_Descripcion AS Ruta, " &
                                  "ce.CLEN_Direccion, at.AGE_Descripcion, '40.2' AS PesoBruto, " &
                                  "c.CLI_Marca, c.CLI_Descripcion, " &
                                  "dbo.FNX_ObtenerAgrupado(d.DIS_Descripcion,p.PRO_Descripcion,de.DEP_Descripcion) AS Agrupacion " &
                                  "FROM Documento_Cliente_Almacen_Detalle dcad " &
                                  "INNER JOIN VW_VistaDocumentoAlmacen da " &
                                  "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                                  "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                                  "INNER JOIN Documento_Cliente dc " &
                                  "ON dc.CIA = dcad.CIA_Cliente AND dc.SUC_Codigo = dcad.SUC_Cliente AND dc.TDOC_Codigo = dcad.TDOC_Cliente " &
                                  "AND dc.DCI_Codigo = dcad.DCI_Codigo " &
                                  "LEFT JOIN Cliente c " &
                                  "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                                  "INNER JOIN Ruta r " &
                                  "ON r.RUT_Codigo = dc.RUT_Codigo " &
                                  "LEFT JOIN ClienteEntrega ce " &
                                  "ON ce.CLEN_Codigo = dc.CLEN_Codigo AND ce.CLI_Codigo = dc.CLI_Codigo AND ce.CLEN_Activo = '1' " &
                                  "LEFT JOIN AgenciaTransporteSucursal ats " &
                                  "ON ats.AGE_Codigo = dc.AGE_Codigo AND ats.ATS_Codigo = dc.ATS_Codigo AND ats.ATS_Activo = '1' " &
                                  "LEFT JOIN AgenciaTransporte at " &
                                  "ON at.AGE_Codigo = dc.AGE_Codigo AND at.AGE_Activo = '1' " &
                                  "LEFT JOIN Distrito d " &
                                  "ON d.DIS_Codigo = ce.DIS_Codigo AND d.DIS_Activo = '1' " &
                                  "LEFT JOIN Provincia p " &
                                  "ON p.PRO_Codigo = d.PRO_Codigo AND p.PRO_Activo = '1' " &
                                  "LEFT JOIN Departamento de " &
                                  "ON de.DEP_Codigo = p.DEP_Codigo AND de.DEP_Activo = '1' " &
                                  "WHERE dcad.DCI_Codigo = @DCI AND dcad.TDOC_Cliente = @TDO AND dcad.DCAD_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", codPedido)
                .Parameters.AddWithValue("@TDO", tipDocumento)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch e As Exception
            ds = Nothing
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerItemsPedido(codPedido As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT a.ART_Codigo,a.ART_Descripcion,dcd.DCD_Atendido,um.UNM_Abreviatura " &
                                  "FROM Documento_Cliente_Detalle dcd " &
                                  "INNER JOIN Articulo a " &
                                  "ON a.ART_Codigo = dcd.ART_Codigo AND a.ART_Activo = '1' " &
                                  "INNER JOIN UnidadMedida um " &
                                  "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                                  "WHERE dcd.DCI_Codigo = @PED AND dcd.DCD_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@PED", codPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

End Class
